package cn.com.acca.ma.dao.impl;

import java.io.UnsupportedEncodingException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.Datum;
import oracle.sql.STRUCT;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;
import org.hibernate.jdbc.Work;

import cn.com.acca.ma.dao.ReportDao;
import cn.com.acca.ma.hibernate.util.HibernateUtil;
import cn.com.acca.ma.model.Report;
import cn.com.acca.ma.model.StockMovingAverage;

public class ReportDaoImpl extends BaseDaoImpl<ReportDaoImpl> implements ReportDao {

	/*********************************************************************************************************************
	 * 
	 * 									                            生成报告
	 * 
	 *********************************************************************************************************************/
	/**
	 * 获取某一日统计的股票样本总数
	 * @param stockDate 日期
	 * @return
	 */
	public int getStockSampleSumByDate(String stockDate){
		logger.info("开始统计日期：【" + stockDate + "】的股票样本总数");
		
		int stockSampleSum;
		String hql = "select count(*) from StockTransactionData as t where t.date=to_date('"+stockDate+"','yyyy-mm-dd')";
		session = HibernateUtil.currentSession();
		session.beginTransaction();
		Query queryStockSampleSum = (Query) session.createQuery(hql);
		stockSampleSum = Integer.parseInt(queryStockSampleSum.uniqueResult().toString());
		session.getTransaction().commit();
		session.close();

		logger.info("日期：【" + stockDate + "】的股票样本总数为【" + stockSampleSum + "】");
		
		return stockSampleSum;
	}

	/**
	 * 获取某一日上涨股票的数量
	 * @param stockDate 日期
	 * @return
	 */
	public int getStockUpMumberByDate(String stockDate){
		logger.info("开始计算日期：【" + stockDate + "】的上涨股票数量");
		
		int stockUpNumber;
		String hql = "select count(*) from StockTransactionData t where t.date=to_date('"+stockDate+"','yyyy-mm-dd') and t.upDown=1";
		session = HibernateUtil.currentSession();
		session.beginTransaction();
		Query queryStockUpNumber = (Query) session.createQuery(hql);
		stockUpNumber = Integer.parseInt(queryStockUpNumber.uniqueResult().toString());
		session.getTransaction().commit();
		session.close();
		
		logger.info("日期：【" + stockDate + "】的上涨股票数量为【" + stockUpNumber + "】");

		return stockUpNumber;
	}

	/**
	 * 获取某一日持平股票的家数
	 * @param stockDate 日期
	 * @return
	 */
	public int getStockMiddleMumberByDate(String stockDate) {
		logger.info("开始计算日期：【" + stockDate + "】的持平股票数量");
		
		int stockMiddleNumber;
		String hql = "select count(*) from StockTransactionData t where t.date=to_date('"+stockDate+"','yyyy-mm-dd') and t.upDown=0";
		session = HibernateUtil.currentSession();
		session.beginTransaction();
		Query queryStockMiddleNumber = (Query) session.createQuery(hql);
		stockMiddleNumber = Integer.parseInt(queryStockMiddleNumber.uniqueResult().toString());
		session.getTransaction().commit();
		session.close();
		
		logger.info("日期：【" + stockDate + "】的上涨股票数量为【" + stockMiddleNumber + "】");

		return stockMiddleNumber;
	}

	/**
	 * 获取某一日下跌股票的家数
	 * @param stockDate 日期
	 * @return
	 */
	public int getStockDownMumberByDate(String stockDate) {
		logger.info("开始计算日期：【" + stockDate + "】的下跌股票数量");
		
		int stockDownNumber;
		String hql = "select count(*) from StockTransactionData t where t.date=to_date('"+stockDate+"','yyyy-mm-dd') and t.upDown=-1";
		session = HibernateUtil.currentSession();
		session.beginTransaction();
		Query queryStockDownNumber = (Query) session.createQuery(hql);
		stockDownNumber = Integer.parseInt(queryStockDownNumber.uniqueResult().toString());
		session.getTransaction().commit();
		session.close();
		
		logger.info("日期：【" + stockDate + "】的持平股票数量为【" + stockDownNumber + "】");

		return stockDownNumber;
	}
	
	/**
	 * 如果REPORT表中有相同的报告记录，则删除
	 * @param report
	 */
	public void deleteReport(Report report){
		logger.info("开始删除报告");
		
		session = HibernateUtil.currentSession();
		session.beginTransaction();
		Criteria criteria=session.createCriteria(Report.class);
		criteria.add(Restrictions.eq("reportDate", report.getReportDate()));
		criteria.add(Restrictions.eq("reportName", report.getReportName()));
		Report persistentReport=(Report) criteria.uniqueResult();
		if(null!=persistentReport){
			session.delete(persistentReport);
			logger.info("日期为【" + report.getReportDate() + "】，名称为【"
				+ report.getReportName() + "】的报告被删除了");
		}
		session.getTransaction().commit();
		session.close();
		
		logger.info("删除报告结束");
	}
	/* Example类和Oracle的CLOB数据类型似乎有冲突，应当尽量避免使用Example类 *
//	public void deleteReport(Report report){
//		logger.info("delete report begin");
//		
//		session = HibernateUtil.currentSession();
//		session.beginTransaction();
//		Criteria criteria=session.createCriteria(Report.class);
//		Example example=Example.create(report);
//		example.excludeProperty("bullRankStockInfo");
//		example.excludeProperty("shortOrderStockInfo");
//		criteria.add(example);
//		Report persistentReport=(Report) criteria.uniqueResult();
//		if(null!=persistentReport){
//			session.delete(persistentReport);
//		}
//		session.getTransaction().commit();
//		session.close();
//		
//		logger.info("delete report finish");
//	}

	/**
	 * 将报告对象保存到数据库
	 * @param report 报告对象
	 */
	public void insertReport(Report report) {
		logger.info("开始向report表插入数据");
		
		session = HibernateUtil.currentSession();
		session.beginTransaction();
		session.saveOrUpdate(report);
		session.getTransaction().commit();
		session.close();
	}
	
	/**
	 * 获取某一日多头排列的股票
	 * @param date
	 * @return 多头排列的股票的
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List getBullRank(String date) {
		logger.info("开始获取日期：【" + date + "】的多头排列的股票");
		
		List<StockMovingAverage> list = new ArrayList<StockMovingAverage>();
		String stringBullRank = new String(
				"select distinct t from StockTransactionData t "
						+ "where t.closePrice>=t.ma5 and t.closePrice>=t.ma10 and t.closePrice>=t.ma20 and t.closePrice>=t.ma60 "
						+ "and t.closePrice>=t.ma120 and t.closePrice>=t.ma250 "
						+ "and t.ma5>=t.ma10 and t.ma5>=t.ma20 and t.ma5>=t.ma60 and t.ma5>=t.ma120 and t.ma5>=t.ma250 "
						+ "and t.ma10>=t.ma20 and t.ma10>=t.ma60 and t.ma10>=t.ma120 and t.ma10>=t.ma250 "
						+ "and t.ma20>=t.ma60 and t.ma20>=t.ma120 and t.ma20>=t.ma250 "
						+ "and t.ma60>=t.ma120 and t.ma60>=t.ma250 "
						+ "and t.ma120>=t.ma250 "
						+ "and t.date=to_date(" + date + ",'yyyy-mm-dd')");
		session = HibernateUtil.currentSession();
		session.beginTransaction();
		Query queryBullRank = (Query) session.createQuery(stringBullRank);
		list = queryBullRank.list();
		session.getTransaction().commit();
		session.close();
		
		return list;
	}

	/**
	 * 获取某一日空头排列的股票
	 * @param date
	 * @return 空头排列的股票的
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List getShortOrder(String date) {
		logger.info("开始获取日期：【" + date + "】的空头排列的股票");
		
		List<StockMovingAverage> list = new ArrayList<StockMovingAverage>();
		String queryString = new String(
				"select distinct t from StockTransactionData t "
						+ "where t.closePrice<=t.ma5 and t.closePrice<=t.ma10 and t.closePrice<=t.ma20 and t.closePrice<=t.ma60 "
						+ "and t.closePrice<=t.ma120 and t.closePrice<=t.ma250 "
						+ "and t.ma5<=t.ma10 and t.ma5<=t.ma20 and t.ma5<=t.ma60 and t.ma5<=t.ma120 and t.ma5<=t.ma250 "
						+ "and t.ma10<=t.ma20 and t.ma10<=t.ma60 and t.ma10<=t.ma120 and t.ma10<=t.ma250 "
						+ "and t.ma20<=t.ma60 and t.ma20<=t.ma120 and t.ma20<=t.ma250 "
						+ "and t.ma60<=t.ma120 and t.ma60<=t.ma250 "
						+ "and t.ma120<=t.ma250 "
						+ "and t.date=to_date(" + date + ",'yyyy-mm-dd')");
		session = HibernateUtil.currentSession();
		session.beginTransaction();
		Query queryShortOrder = (Query) session.createQuery(queryString);
		list = queryShortOrder.list();
		session.getTransaction().commit();
		session.close();
		
		return list;
	}
	
	/**
	 * 查询某一周MACD底背离的股票
	 * @param weekendMacdEndDeviateBeginDate
	 * @param weekendMacdEndDeviateEndDate
	 * @return 周线级别底背离股票的信息列表
	 */
	public String findWeekendMacdEndDeviate(final String weekendMacdEndDeviateBeginDate,final String weekendMacdEndDeviateEndDate,final String reportWeekendMacdEndDeviateRate){
		logger.info("查询某一周（日期：从【" + weekendMacdEndDeviateBeginDate + "】至【" + weekendMacdEndDeviateEndDate + "】），MACD底背离的股票");
		
		session= HibernateUtil.currentSession();
		session.beginTransaction();
		final StringBuilder sb = new StringBuilder();
		//定义一个匿名类，实现了Work接口
		session.doWork(new Work(){
			public void execute(Connection connection)throws SQLException{
				//通过JDBC API执行用于批量更新的SQL语句
				CallableStatement cstmt=connection.prepareCall("{call pkg_week.FIND_WEEK_MACD_END_DEVIATE(?,?,?,?)}");
				cstmt.setString(1, weekendMacdEndDeviateBeginDate);
				cstmt.setString(2, weekendMacdEndDeviateEndDate);
				cstmt.setString(3, reportWeekendMacdEndDeviateRate);
				cstmt.registerOutParameter(4, Types.CLOB);
				cstmt.executeUpdate();
				sb.append(cstmt.getString(4));
			}
		});
		session.getTransaction().commit();
		session.close();
		
		return sb.toString();
	}

	public String selectReverseStock(final String stockDate,final double percentage) {
		logger.info("select reverse stock begin");
		
		final StringBuilder result=new StringBuilder();
		session= HibernateUtil.currentSession();
		session.beginTransaction();
		session.doWork(new Work(){
			public void execute(Connection connection) throws SQLException{
				DatabaseMetaData dmd = connection.getMetaData();
				Connection metaDataConnection = null;
				if(dmd != null){
				    metaDataConnection = dmd.getConnection();
				}
				if(!(metaDataConnection instanceof OracleConnection)){
				    logger.error("Connection is not instance of OracleConnection, returning");
				    return; /* Not connection u want */
				}
				OracleConnection oracleConnection = (OracleConnection)metaDataConnection;
				OracleCallableStatement cstmt = (OracleCallableStatement) oracleConnection.prepareCall("{call pkg_stock_transaction_data.Select_Reverse_Stock(?,?,?)}");
				cstmt.setString(1, stockDate);
				cstmt.setDouble(2, percentage);
				cstmt.registerOutParameter(3, OracleTypes.ARRAY, "T_TYPE_ARRAY");
				cstmt.execute();
				ARRAY array = cstmt.getARRAY(3);
				Datum[] datas = array.getOracleArray();
				
				for (int i = 0; i < datas.length; i++){
					result.append(new String(datas[i].getBytes())+"\t");
		        }
			}
		});
		session.getTransaction().commit();
		session.close();
		
		logger.info("select reverse stock finish");
		
		return result.toString();
	}

	/**
	 * 查找日线级别MACD金叉的股票
	 * @param macdDate 日期
	 * @param macdRate 下跌幅度
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public List selectMACDGoldCross(final String macdDate,final String macdRate){
		logger.info("开始查找日期:【" + macdDate + "】，日线级别MACD金叉的股票");
		
		final List list=new ArrayList();
		session= HibernateUtil.currentSession();
		session.beginTransaction();
		session.doWork(new Work(){
			@SuppressWarnings("unchecked")
			public void execute(Connection connection)throws SQLException{
				DatabaseMetaData dmd = connection.getMetaData();
				Connection metaDataConnection = null;
				if(dmd != null){
				    metaDataConnection = dmd.getConnection();
				}
				if(!(metaDataConnection instanceof OracleConnection)){
				    logger.error("Connection不是OracleConnection的实例，方法返回");
				    return;
				}
				OracleConnection oracleConnection = (OracleConnection)metaDataConnection;
				OracleCallableStatement cstmt = (OracleCallableStatement) oracleConnection.prepareCall("{call pkg_stock_transaction_data.SELECT_MACD_GOLD_CROSS(?,?,?)}");
				cstmt.setString(1, macdDate);
				cstmt.setString(2, macdRate);
				cstmt.registerOutParameter(3, OracleTypes.ARRAY, "T_TYPE_ARRAY");
				cstmt.execute();
				ARRAY array = cstmt.getARRAY(3);
				Datum[] datas = array.getOracleArray();
				
				for (int i = 0; i < datas.length; i++){
					list.add(new String(datas[i].getBytes()));
		        }
			}
		});
		session.getTransaction().commit();
		session.close();
		
		return list;
	}
	
	/**
	 * 获取周线级别KD指标金叉的股票
	 * @param beginDate
	 * @param endDate
	 * @param crossPoint
	 * @return
	 */
	public StringBuilder selectStockWeekendKDUp(final String beginDate,final String endDate,final String crossPoint){
		logger.info("开始查找周线级别（开始时间：【" + beginDate + "】，结束时间：【" + endDate
			+ "】）KD指标金叉的股票");
		
		final StringBuilder result=new StringBuilder();
		session= HibernateUtil.currentSession();
		session.beginTransaction();
		session.doWork(new Work(){
			public void execute(Connection connection)throws SQLException{
				DatabaseMetaData databaseMetadata= connection.getMetaData();
				OracleConnection oracleConnection=(OracleConnection) databaseMetadata.getConnection();
				OracleCallableStatement ocs=(OracleCallableStatement) oracleConnection.prepareCall("{call pkg_week.SELECT_WEEK_KD_UP(?,?,?,?)}");
				ocs.setString(1, beginDate);
				ocs.setString(2, endDate);
				ocs.setInt(3, Integer.valueOf(crossPoint));
				ocs.registerOutParameter(4, oracle.jdbc.OracleTypes.ARRAY,"T_STOCK_WEEK_RESULT_ARRAY");
				ocs.execute();
				ARRAY array=ocs.getARRAY(4);
				Datum[] data=array.getOracleArray();
				if(data.length>0){
					for(int i=0;i<data.length;i++){
						if(null!=data[i]&&((STRUCT)data[i])!=null){
							Datum[] stockWeekendResult=((STRUCT)data[i]).getOracleAttributes();
							result.append(new String(stockWeekendResult[0].getBytes())+","+stockWeekendResult[1].dateValue()+","+stockWeekendResult[2].dateValue()+"\n");
						}
					}
				}
			}
		});
		session.getTransaction().commit();
		session.close();
		
		return result;
	}
	
	/**
	 * 查询某一日收盘价出现异常的股票
	 * @param stockDate 日期
	 * @param rate 倍数
	 * @return
	 */
	public StringBuilder findAbnormalStock(final String stockDate, final int rate) {
        logger.info("查询【" + stockDate + "】收盘价出现异常的股票");
        
        final StringBuilder result=new StringBuilder();
        session=HibernateUtil.currentSession();
        session.beginTransaction();
        session.doWork(new Work(){
            public void execute(Connection connection) throws SQLException{
                DatabaseMetaData dm=connection.getMetaData();
                Connection metaConnection=null;
                if(null!=dm){
                    metaConnection=dm.getConnection();
                }
                if(!(metaConnection instanceof OracleConnection)){
                    logger.error("Connection不是OracleConnection的实例，方法返回");
                    return;
                }
                OracleConnection oracleConnection=(OracleConnection)metaConnection;
                OracleCallableStatement ocs=(OracleCallableStatement) oracleConnection.prepareCall("{call pkg_stock_transaction_data.FIND_ABNORMAL_STOCK(?,?,?)}");
                ocs.setString(1, stockDate);
                ocs.setInt(2, rate);
                ocs.registerOutParameter(3, OracleTypes.ARRAY,"T_STOCK_RESULT_ARRAY");
                ocs.execute();
                ARRAY array=ocs.getARRAY(3);
                Datum[] datas = array.getOracleArray();
                if(datas.length>0){
                    for (int i = 0; i < datas.length; i++){
                        if(datas[i]!=null&&((STRUCT) datas[i])!=null){
	                        Datum[] stockResultAttributes = ((STRUCT) datas[i]).getOracleAttributes();
	                        result.append(new String(stockResultAttributes[0].getBytes())+","+stockResultAttributes[1].dateValue()+"\n");
                        }else{
                            logger.info("datas["+i+"]是null.");
                        }
                    }
                }else{
                	logger.info("存储过程pkg_stock_transaction_data.FIND_ABNORMAL_STOCK没有返回结果");
                }
            }
        });
	    session.getTransaction().commit();
	    session.close();

	    return result;
    }
    
	/**
	 * 查询某一日MACD指标最低的stockNumber支股票
	 * @param stockDate 日期（结束日期）
	 * @param stockNumber 股票个数
	 * @param intervalDate stockDate之前intervalDate个交易日（开始日期）
	 * @return
	 */
	public String findLowestMacdStock(final String stockDate, final int stockNumber, final int intervalDate){
		logger.info("查询【" + stockDate + "】MACD指标最低的【" + stockNumber + "】支股票");

        final StringBuffer result=new StringBuffer();
		session = HibernateUtil.currentSession();
		session.beginTransaction();
		
		session.doWork(new Work(){
            public void execute(Connection connection) throws SQLException{
                DatabaseMetaData dm=connection.getMetaData();
                Connection metaConnection=null;
                if(null!=dm){
                    metaConnection=dm.getConnection();
                }
                if(!(metaConnection instanceof OracleConnection)){
                    logger.error("Connection不是OracleConnection的实例，方法返回");
                    return; /* Not connection u want */
                }
                OracleConnection oracleConnection=(OracleConnection)metaConnection;
                OracleCallableStatement ocs=(OracleCallableStatement) oracleConnection.prepareCall("{call pkg_stock_transaction_data.FIND_LOWEST_MACD_STOCK(?,?,?,?)}");
                ocs.setString(1, stockDate);
                ocs.setInt(2, stockNumber);
                ocs.setInt(3, intervalDate);
                ocs.registerOutParameter(4, OracleTypes.ARRAY,"T_STOCK_RESULT_ARRAY");
                ocs.execute();
                ARRAY array=ocs.getARRAY(4);
                Datum[] datas = array.getOracleArray();
                if(datas.length>0){
                    for (int i = 0; i < datas.length; i++){
                        if(datas[i]!=null&&((STRUCT) datas[i])!=null){
	                        Datum[] stockResultAttributes = ((STRUCT) datas[i]).getOracleAttributes();
	                        result.append(new String(stockResultAttributes[0].getBytes())+","+stockResultAttributes[1].dateValue()+"\n");
                        }else{
                            logger.info("datas["+i+"]是null.");
                        }
                    }
                }else{
                	logger.info("存储过程pkg_stock_transaction_data.FIND_LOWEST_MACD_STOCK没有返回结果");
                }
            }
        });

		session.getTransaction().commit();
		session.close();
		
		return result.toString();
	}
	
	/**
	 * 查询某一周最低KD的stockNumber只股票
	 * @param beginDate
	 * @param endDate
	 * @param timeSpan
	 * @param stockNumber
	 * @return
	 */
	public String findLowestKdStockWeek(String beginDate,String endDate,String timeSpan,int stockNumber){
        logger.info("查询某一周（开始时间：【" + beginDate + "】，结束时间：【" + endDate
			+ "】）最低KD的【" + stockNumber + "】只股票");
    
        session= HibernateUtil.currentSession();
        session.beginTransaction();
        StringBuffer queryString=new StringBuffer(""
			+ "select t.code "
			+ "from StockWeek t "
			+ "where t.beginDate=to_date(?,'yyyy-mm-dd') and t.endDate=to_date(?,'yyyy-mm-dd') " +
        		"and t.code not in("
					+ "select distinct t1.code "
					+ "from StockTransactionData t1 "
					+ "where (t1.closePrice - t1.lastClosePrice) / t1.lastClosePrice <=-11 "
						+ "and t1.date between to_date(?,'yyyy-mm-dd') and to_date(?,'yyyy-mm-dd')"
				+ ") "
				+ "and t.k<=t.d order by (t.k+t.d)/2 asc");
        Query query = session.createQuery(queryString.toString());
        query.setString(0, beginDate);
        query.setString(1, endDate);
        query.setString(2, timeSpan);
        query.setString(3, endDate);
        query.setMaxResults(stockNumber);
        String result=query.list().toString();
        session.getTransaction().commit();
        session.close();
    
        return result;
    }
	
	/**
	 * 查询某一日股票收盘价到达250日均线支撑的股票
	 * @param stockDate 日期
	 * @param nearRate 接近250日均线的比率
	 * @param downRate 之前下跌的比率
	 * @return
	 */
	public StringBuilder selectStockWith250Support(final String stockDate,final double nearRate,final double downRate){
		logger.info("select stock with 250 support begin");
		
		final StringBuilder result=new StringBuilder();
        session=HibernateUtil.currentSession();
        session.beginTransaction();
        session.doWork(new Work(){
            public void execute(Connection connection) throws SQLException{
                DatabaseMetaData dm=connection.getMetaData();
                Connection metaConnection=null;
                if(null!=dm){
                    metaConnection=dm.getConnection();
                }
                if(!(metaConnection instanceof OracleConnection)){
                    logger.error("Connection is not instance of OracleConnection, returning");
                    return; /* Not connection u want */
                }
                OracleConnection oracleConnection=(OracleConnection)metaConnection;
                OracleCallableStatement ocs=(OracleCallableStatement) oracleConnection.prepareCall("{call pkg_stock_transaction_data.SELECT_STOCK_WITH_250_SUPPORT(?,?,?,?)}");
                ocs.setString(1, stockDate);
                ocs.setDouble(2, nearRate);
                ocs.setDouble(3, downRate);
                ocs.registerOutParameter(4, OracleTypes.ARRAY,"T_STOCK_RESULT_ARRAY");
                ocs.execute();
                ARRAY array=ocs.getARRAY(4);
                Datum[] datas = array.getOracleArray();
                if(datas.length>0){
                    for (int i = 0; i < datas.length; i++){
                        if(datas[i]!=null&&((STRUCT) datas[i])!=null){
	                        Datum[] stockResultAttributes = ((STRUCT) datas[i]).getOracleAttributes();
	                        result.append(new String(stockResultAttributes[0].getBytes())+","+stockResultAttributes[1].dateValue()+"\n");
                        }else{
                            logger.info("datas["+i+"] is null.");
                        }
                    }
                }else{
                	logger.info("this procedure is not result data...");
                }
            }
        });
	    session.getTransaction().commit();
	    session.close();
		
		logger.info("select stock with 250 support finish");
		return result;
	}
	
	/**
	 * 查询某一日股票收盘价到达120日均线支撑的股票
	 * @param stockDate 日期
	 * @param nearRate 接近120日均线的比率
	 * @param downRate 之前下跌的比率
	 * @return
	 */
	public StringBuilder selectStockWith120Support(final String stockDate,final double nearRate,final double downRate){
		logger.info("select stock with 120 support begin");
		
		final StringBuilder result=new StringBuilder();
        session=HibernateUtil.currentSession();
        session.beginTransaction();
        session.doWork(new Work(){
            public void execute(Connection connection) throws SQLException{
                DatabaseMetaData dm=connection.getMetaData();
                Connection metaConnection=null;
                if(null!=dm){
                    metaConnection=dm.getConnection();
                }
                if(!(metaConnection instanceof OracleConnection)){
                    logger.error("Connection is not instance of OracleConnection, returning");
                    return; /* Not connection u want */
                }
                OracleConnection oracleConnection=(OracleConnection)metaConnection;
                OracleCallableStatement ocs=(OracleCallableStatement) oracleConnection.prepareCall("{call pkg_stock_transaction_data.SELECT_STOCK_WITH_120_SUPPORT(?,?,?,?)}");
                ocs.setString(1, stockDate);
                ocs.setDouble(2, nearRate);
                ocs.setDouble(3, downRate);
                ocs.registerOutParameter(4, OracleTypes.ARRAY,"T_STOCK_RESULT_ARRAY");
                ocs.execute();
                ARRAY array=ocs.getARRAY(4);
                Datum[] datas = array.getOracleArray();
                if(datas.length>0){
                    for (int i = 0; i < datas.length; i++){
                        if(datas[i]!=null&&((STRUCT) datas[i])!=null){
	                        Datum[] stockResultAttributes = ((STRUCT) datas[i]).getOracleAttributes();
	                        result.append(new String(stockResultAttributes[0].getBytes())+","+stockResultAttributes[1].dateValue()+"\n");
                        }else{
                            logger.info("datas["+i+"] is null.");
                        }
                    }
                }else{
                	logger.info("this procedure is not result data...");
                }
            }
        });
	    session.getTransaction().commit();
	    session.close();
		
		logger.info("select stock with 120 support finish");
		return result;
	}
	
	/**
	 * 查询某一段时间内上涨股票的前number名
	 * @param beginDate
	 * @param endDate
	 * @param number
	 * @return
	 */
	public StringBuilder findTopStock(final String beginDate,final String endDate,final int number,final int topStockWordLimitNumber){
		logger.info("find top stock begin");
		
		final StringBuilder result=new StringBuilder();
		session = HibernateUtil.currentSession();
		session.beginTransaction();
		session.doWork(new Work(){
            public void execute(Connection connection) throws SQLException{
                DatabaseMetaData dm=connection.getMetaData();
                Connection metaConnection=null;
                if(null!=dm){
                    metaConnection=dm.getConnection();
                }
                if(!(metaConnection instanceof OracleConnection)){
                    logger.error("Connection is not instance of OracleConnection, returning");
                    return; /* Not connection u want */
                }
                OracleConnection oracleConnection=(OracleConnection)metaConnection;
                OracleCallableStatement ocs=(OracleCallableStatement) oracleConnection.prepareCall("{call pkg_stock_transaction_data.FIND_TOP_STOCK(?,?,?,?,?)}");
                ocs.setString(1, beginDate);
                ocs.setString(2, endDate);
                ocs.setInt(3, number);
                ocs.setInt(4, topStockWordLimitNumber);
                ocs.registerOutParameter(5, OracleTypes.ARRAY,"T_STOCK_TOP_RESULT_ARRAY");
                ocs.execute();
                ARRAY array=ocs.getARRAY(5);
                Datum[] datas = array.getOracleArray();
                if(datas.length>0){
                    for (int i = 0; i < datas.length; i++){
                        if(datas[i]!=null&&((STRUCT) datas[i])!=null){
	                        Datum[] stockResultAttributes = ((STRUCT) datas[i]).getOracleAttributes();
	                        try {
								result.append(new String(stockResultAttributes[0].getBytes(), "utf-8")
									+ ","
									+ new String(stockResultAttributes[1].getBytes(), "utf-8")
									+ ","
									+ new String(stockResultAttributes[2].getBytes(), "utf-8")
									+ ","
									+ stockResultAttributes[3].bigDecimalValue() + "\n");
							}catch (UnsupportedEncodingException e){
	                        	e.printStackTrace();
							}
                        }else{
                            logger.info("datas["+i+"] is null.");
                        }
                    }
                }else{
                	logger.info("this procedure is not result data...");
                }
            }
        });
		session.getTransaction().commit();
		session.close();
		
		logger.info("find top stock finish");
		return result;
	}
	
	/**
	 * 查询某一段时间内下跌股票的前number名
	 * @param beginDate
	 * @param endDate
	 * @param number
	 * @return
	 */
	public StringBuilder findLastStock(final String beginDate,final String endDate,final int number){
		logger.info("find last stock begin");
		
		final StringBuilder result=new StringBuilder();
		session = HibernateUtil.currentSession();
		session.beginTransaction();
		session.doWork(new Work(){
            public void execute(Connection connection) throws SQLException{
                DatabaseMetaData dm=connection.getMetaData();
                Connection metaConnection=null;
                if(null!=dm){
                    metaConnection=dm.getConnection();
                }
                if(!(metaConnection instanceof OracleConnection)){
                    logger.error("Connection is not instance of OracleConnection, returning");
                    return; /* Not connection u want */
                }
                OracleConnection oracleConnection=(OracleConnection)metaConnection;
                OracleCallableStatement ocs=(OracleCallableStatement) oracleConnection.prepareCall("{call pkg_stock_transaction_data.FIND_LAST_STOCK(?,?,?,?)}");
                ocs.setString(1, beginDate);
                ocs.setString(2, endDate);
                ocs.setInt(3, number);
                ocs.registerOutParameter(4, OracleTypes.ARRAY,"T_STOCK_LAST_RESULT_ARRAY");
                ocs.execute();
                ARRAY array=ocs.getARRAY(4);
                Datum[] datas = array.getOracleArray();
                if(datas.length>0){
                    for (int i = 0; i < datas.length; i++){
                        if(datas[i]!=null&&((STRUCT) datas[i])!=null){
	                        Datum[] stockResultAttributes = ((STRUCT) datas[i]).getOracleAttributes();
	                        try {
								result.append(new String(stockResultAttributes[0].getBytes(),"utf-8")
									+ ","
									+ new String(stockResultAttributes[1].getBytes(),"utf-8")
									+ ","
									+ new String(stockResultAttributes[2].getBytes(),"utf-8")
									+ ","
									+ stockResultAttributes[3].bigDecimalValue() + "\n");
							}catch (UnsupportedEncodingException e){
	                        	e.printStackTrace();
							}
                        }else{
                            logger.info("datas["+i+"] is null.");
                        }
                    }
                }else{
                	logger.info("this procedure is not result data...");
                }
            }
        });
		session.getTransaction().commit();
		session.close();
		
		logger.info("find last stock finish");
		return result;
	}
	
	/*********************************************************************************************************************
	 * 
	 * 									                           增量备份表REPORT
	 * 
	 *********************************************************************************************************************/
	/**
	 * 从表REPORT中获取从开始时间beginDate到结束时间endDate的report_date字段值
	 * @param beginDate
	 * @param endDate
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Date> getDateByCondition(String beginDate, String endDate){
		logger.info("从表REPORT中获取从开始时间【" + beginDate + "】到结束时间【" + endDate + "】的report_date字段值");
	    
        session= HibernateUtil.currentSession();
        session.beginTransaction();
        StringBuffer queryString=new StringBuffer("select distinct t.reportDate from Report t " +
        		"where t.reportDate between to_date(?,'yyyy-mm-dd') and to_date(?,'yyyy-mm-dd') order by t.reportDate asc");
        Query query = session.createQuery(queryString.toString());
        query.setString(0, beginDate);
        query.setString(1, endDate);
        List<Date> result=query.list();
        session.getTransaction().commit();
        session.close();
    
        return result;
	}
	
	/**
	 * 根据日期获取Report对象
	 * @param date
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Report> getReportByDate(String date){
		logger.info("根据日期【" + date + "】获取Report对象");
	    
        session= HibernateUtil.currentSession();
        session.beginTransaction();
        StringBuffer queryString=new StringBuffer("select t from Report t " +
        		"where t.reportDate=to_date(?,'yyyy-mm-dd') order by t.reportDate asc");
        Query query = session.createQuery(queryString.toString());
        query.setString(0, date);
        List<Report> result=query.list();
        session.getTransaction().commit();
        session.close();
    
        return result;
	}
}
